package com.tech.common

import com.tech.config.ApplicationConfig
import com.tech.process.nodeProcess._

import java.sql.{Connection, DriverManager}
import scala.collection.mutable
import scala.collection.mutable.ArrayBuffer

object MysqlUtil {

  val connection: Connection = DriverManager.getConnection(ApplicationConfig.URL_MYSQL)

  def main(args: Array[String]): Unit = {
    val strings = getActivity(connection)
    println(strings)
  }

  /**
   * 获取活跃活动
   *
   * @return 活跃活动ID
   */
  def getActivity(connection: Connection): ArrayBuffer[String] = {

    val statement = connection.createStatement()
    val set = statement.executeQuery {
      "select id from tsr_marketing_activity where status = '100000002' and current_timestamp() between begin_time and end_time and (activity_type = 0 or (activity_type = 1 and ((period_unit = 100000000 and period_value = dayofyear(curdate())) or (period_unit = 100000001 and period_value = dayofmonth(curdate())) or (period_unit = 100000002 and period_value = date_format(curdate(),'%w')) or period_unit = 100000003)))"
    }
    val array = new ArrayBuffer[String]()
    while (set.next()) {
      array.append(set.getString("id"))
    }
    statement.close()
    array
  }


  /**
   * 获取客户节点
   *
   * @param activity_id 活动id
   * @return
   */
  def getGroupNodes(connection: Connection, activity_id: String): mutable.HashMap[String, String] = {

    val statement = connection.createStatement()
    println(s"select process_id,customer_group_id from tsr_marketing_process_customer where activity_id = $activity_id")
    val set = statement.executeQuery {
      s"select process_id,customer_group_id from tsr_marketing_process_customer where activity_id = '$activity_id'"
    }
    val map = mutable.HashMap[String, String]()
    while (set.next()) {
      map.put(set.getString("process_id"), set.getString("customer_group_id"))
    }
    statement.close()
    map
  }

  /**
   * 获取客户节点(静态群组)
   *
   * @param activity_id 活动id
   * @return
   */
  def getGroupNodesWithOutDynamic(connection: Connection, activity_id: String): mutable.HashMap[String, String] = {

    val statement = connection.createStatement()
    val set = statement.executeQuery {
      s"select t.process_id,t.customer_group_id from tsr_marketing_process_customer t left join shuat_datanow.customergroups c on t.customer_group_id = c.id where c.group_type = 100000000 and t.activity_id = '$activity_id'"
    }
    val map = mutable.HashMap[String, String]()
    while (set.next()) {
      map.put(set.getString("process_id"), set.getString("customer_group_id"))
    }
    statement.close()
    map
  }

  /**
   * 获取行为节点及其对应事件类型
   *
   * @param activities 活跃活动
   * @return Map（节点id -> 事件类型）
   */
  def getBehaviorMap(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, String] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    println(s"select process_id,event_type from tsr_marketing_process_behavior where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select process_id,event_type from tsr_marketing_process_behavior where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, String]
    while (set.next()) {
      map.put(set.getString("process_id"), set.getString("event_type"))
    }
    statement.close()
    map
  }

  /**
   * 获取开始节点
   *
   * @param activities 活跃活动
   * @return Map（活动id -> 开始节点id）
   */
  def getStartNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, String] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select activity_id,id from tsr_marketing_activity_process where activity_id in (${activity.substring(1)}) and prev_node is null")
    val set = statement.executeQuery {
      s"select activity_id,id from tsr_marketing_activity_process where activity_id in (${activity.substring(1)}) and prev_node is null"
    }
    val map = new mutable.HashMap[String, String]
    while (set.next()) {
      map.put(set.getString("activity_id"), set.getString("id"))
    }
    statement.close()
    map
  }

  /**
   * 获取活动节点、下级节点
   *
   * @param activities 活跃活动
   * @return Map（节点id -> 下级节点信息（NextNode））
   */
  def getNextNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, NextNode] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    println(s"select activity_id,id,type,next_node from tsr_marketing_activity_process where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select activity_id,id,type,next_node from tsr_marketing_activity_process where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, NextNode]()
    while (set.next()) {
      val node = new NextNode(
        set.getString("activity_id"),
        set.getString("id"),
        set.getInt("type"),
        set.getString("next_node")
      )
      map.put(set.getString("id"), node)
    }
    statement.close()
    map
  }

  /**
   * 获取客群
   *
   * @param activities 活跃活动
   * @return Map（客户节点id -> Set(客户id)）
   */
  def getGroups(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, mutable.Set[String]] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select process_id,customer_group_id from tsr_marketing_process_customer where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select process_id,customer_group_id from tsr_marketing_process_customer where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, mutable.Set[String]]
    while (set.next()) {
      if (!map.keySet.contains(set.getString("process_id")))
        map.put(set.getString("customer_group_id"), KuduUtil.getIdsByGroupId(set.getString("customer_group_id")))
    }
    statement.close()
    map
  }

  /**
   * 获取全部行为节点规则
   *
   * @param activities 活跃活动
   * @return Map（行为节点id -> 行为节点规则(BehaviorNode)）
   */
  def getBehaviorNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, BehaviorNode] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select activity_id,process_id,time_type,time_condition,customer_behavior,event_type,event_name,addition,measure_property,behavior_attribute from tsr_marketing_process_behavior where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select activity_id,process_id,time_type,time_condition,customer_behavior,event_type,event_name,addition,measure_property,behavior_attribute from tsr_marketing_process_behavior where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, BehaviorNode]()
    while (set.next()) {
      val node = new BehaviorNode(
        set.getString("activity_id"),
        set.getString("process_id"),
        set.getInt("time_type"),
        set.getString("time_condition"),
        set.getInt("customer_behavior"),
        set.getString("event_type"),
        set.getString("event_name"),
        set.getString("addition"),
        set.getString("measure_property"),
        set.getString("behavior_attribute")
      )
      map.put(set.getString("process_id"), node)
    }
    statement.close()
    map
  }

  /**
   * 获取全部客户节点规则
   *
   * @param activities 活跃活动
   * @return Map（客户节点id -> 客户节点规则(CustomerNode)）
   */
  def getCustomerNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, CustomerNode] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select activity_id,process_id,customer_group_id,customer_group_name from tsr_marketing_process_customer where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select activity_id,process_id,customer_group_id,customer_group_name from tsr_marketing_process_customer where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, CustomerNode]()
    while (set.next()) {
      val node = new CustomerNode(
        set.getString("activity_id"),
        set.getString("process_id"),
        set.getString("customer_group_id"),
        set.getString("customer_group_name")
      )
      map.put(set.getString("process_id"), node)
    }
    statement.close()
    map
  }

  /**
   * 获取全部消息节点规则
   *
   * @param activities 活跃活动
   * @return Map（消息节点id -> 消息节点规则(MessageNode)）
   */
  def getMessageNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, MessageNode] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select activity_id,process_id,type,send_type,send_time,sms_template_id,wechat_template_id,message_send_type,send_priority from tsr_marketing_process_message where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select activity_id,process_id,type,send_type,send_time,sms_template_id,wechat_template_id,message_send_type,send_priority from tsr_marketing_process_message where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, MessageNode]()
    while (set.next()) {
      val node = new MessageNode(
        set.getString("activity_id"),
        set.getString("process_id"),
        set.getInt("type"),
        set.getInt("send_type"),
        set.getDate("send_time"),
        set.getString("sms_template_id"),
        set.getString("wechat_template_id"),
        set.getInt("message_send_type"),
        set.getString("send_priority")
      )
      map.put(set.getString("process_id"), node)
    }
    statement.close()
    map
  }

  /**
   * 获取全部奖励节点规则
   *
   * @param activities 活跃活动
   * @return Map（奖励节点id -> 奖励节点规则(RewardNode)）
   */
  def getRewardNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, RewardNode] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select activity_id,process_id,type,coupon_rule_id,coupon_publish_count,point_group_id,point_num,join_upgrade,point_invalid_type,integration_period from tsr_marketing_process_reward where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select activity_id,process_id,type,coupon_rule_id,coupon_publish_count,point_group_id,point_num,join_upgrade,point_invalid_type,integration_period from tsr_marketing_process_reward where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, RewardNode]()
    while (set.next()) {
      val node = new RewardNode(
        set.getString("activity_id"),
        set.getString("process_id"),
        set.getInt("type"),
        set.getString("coupon_rule_id"),
        set.getInt("coupon_publish_count"),
        set.getString("point_group_id"),
        set.getLong("point_num"),
        set.getInt("join_upgrade"),
        set.getInt("point_invalid_type"),
        set.getString("integration_period")
      )
      map.put(set.getString("process_id"), node)
    }
    statement.close()
    map
  }

  /**
   * 获取全部 A/B TEST 节点规则
   *
   * @param activities 活跃活动
   * @return Map（A/B TEST节点id -> A/B TEST节点规则(TestNode)）
   */
  def getTestNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, TestNode] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select activity_id,process_id,probability from tsr_marketing_process_test where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select activity_id,process_id,probability from tsr_marketing_process_test where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, TestNode]()
    while (set.next()) {
      val node = new TestNode(
        set.getString("activity_id"),
        set.getString("process_id"),
        set.getInt("probability")
      )
      map.put(set.getString("process_id"), node)
    }
    statement.close()
    map
  }


  /**
   * 获取全部等待节点规则
   *
   * @param activities 活跃活动
   * @return Map（等待节点id -> 等待节点规则(WaitNode)）
   */
  def getWaitNodes(connection: Connection, activities: ArrayBuffer[String]): mutable.HashMap[String, WaitNode] = {

    val statement = connection.createStatement()
    var activity = ""
    activities.foreach(i =>
      activity += (",\'" + i + "\'")
    )

    if (activity == "") {
      activity += ",\'\'"
    }
    //   println(s"select activity_id,process_id,type,wait_duration,wait_date,unit from tsr_marketing_process_wait where activity_id in (${activity.substring(1)})")
    val set = statement.executeQuery {
      s"select activity_id,process_id,type,wait_duration,UNIX_TIMESTAMP(wait_date) wait_date,unit from tsr_marketing_process_wait where activity_id in (${activity.substring(1)})"
    }
    val map = new mutable.HashMap[String, WaitNode]()
    while (set.next()) {
      val node = new WaitNode(
        set.getString("activity_id"),
        set.getString("process_id"),
        set.getInt("type"),
        set.getInt("wait_duration"),
        set.getLong("wait_date"),
        set.getInt("unit")
      )
      map.put(set.getString("process_id"), node)
    }
    statement.close()
    map
  }

  //  def main(args: Array[String]): Unit = {
  //
  //    val statement = connection.createStatement()
  //    val set = statement.executeQuery {
  //      s"select activity_id,process_id,type,wait_duration,UNIX_TIMESTAMP(wait_date) wait_date,unit from tsr_marketing_process_wait where activity_id = '5f491ee4-797c-4c58-b67d-64a9fd5a6b17'"
  //    }
  //    val map = new mutable.HashMap[String, WaitNode]()
  //    while (set.next()) {
  //      val node = new WaitNode(
  //        set.getString("activity_id"),
  //        set.getString("process_id"),
  //        set.getInt("type"),
  //        set.getInt("wait_duration"),
  //        set.getLong("wait_date"),
  //        set.getInt("unit")
  //      )
  //      map.put(set.getString("process_id"), node)
  //    }
  //    statement.close()
  //    println(map)
  //
  //  }


}


